<?php
/**
 * Description
 * User: duanwenjie
 * Date: 2019/6/9
 * Time: 11:59 AM
 */

namespace app\index\service;
use app\common\ConfigBase;
use app\common\model\ToolModel;
use app\common\service\DownService;
use app\common\service\SkuService;
use think\Db;
use think\Exception;

class InventoryService
{
    static public function getList($param, $is_download = false)
    {
        try{
            $where = "where 1 = 1 ";
            $pageNumber = isset($param['page']) ? $param['page'] : 1;
            $pageData = isset($param['limit']) ? $param['limit'] : 20;
            $id = isset($param['id']) ? $param['id'] : '';  // ID
            $inventory_sn = isset($param['inventory_sn']) ? $param['inventory_sn'] : '';  //盘点单单号
            $warehouse_id = isset($param['warehouse_id']) ? $param['warehouse_id'] : '';  // warehouse_id
            $operator = isset($param['operator']) ? $param['operator'] : '';  //盘点人
            $type = isset($param['type']) ? $param['type'] : '';  //盘点类型
            $status = isset($param['status']) ? $param['status'] : '';  //审核状态
            $complete_time = isset($param['complete_time']) ? $param['complete_time'] : '';  //完成时间
            $offset = ($pageNumber - 1) * $pageData;
            $limit = " LIMIT $offset,$pageData";

            if (!empty($id)) {
                $id = trim($id);
                $where .= " AND inventory.id = $id";
            }
            if (!empty($inventory_sn)) {
                $inventory_sn = trim($inventory_sn);
                $where .= " AND inventory.inventory_sn = '{$inventory_sn}'";
            }
            if (!empty($operator)) {
                $operator = trim($operator);
                $where .= " AND inventory.operator = '{$operator}'";
            }
            if (!empty($warehouse_id)) {
                $warehouse_id = trim($warehouse_id);
                $where .= " AND inventory.warehouse_id = $warehouse_id";
            }
            if (!empty($type)) {
                $type = trim($type);
                $where .= " AND inventory.type = $type";
            }
            if (!empty($status)) {
                $status = trim($status);
                $where .= " AND inventory.status = $status";
            }
            if (!empty($complete_time)) {
                $date = explode(' - ', $complete_time);
                if (count($date) == 2){
                    $where .= " AND inventory.complete_time >= '$date[0]'";
                    $where .= " AND inventory.complete_time <= '$date[1]'";
                }
            }

            $sqlCount = "SELECT COUNT(id) num FROM inventory {$where}";
            $countArr = ToolModel::queryData($sqlCount);
            if (empty($countArr) || $countArr[0]['num'] == 0) {
                if ($is_download) exitJson(["code" => "500", "msg" => "无有效数据"]);
                return array('total' => 0, 'list' => array());
            }
            $fileds = 'inventory.id,inventory_sn,inventory.warehouse_id,base_warehouse.warehouse_name,base_warehouse.type as ware_type,inventory.difference_quantity_all,
            inventory.difference_money_all,inventory.operator,inventory.status,inventory.type,case inventory.type 
            when 10 then "全库盘点" when 20 then "SKU盘点" end type_name,
            case inventory.status when 10 then "待提交" when 20 then "待审核" when 30 then "审核通过" when 40 
            then "审核不通过" end status_name,inventory.create_time,inventory.complete_time';

            $sql = "SELECT {$fileds} FROM inventory LEFT JOIN base_warehouse ON inventory.warehouse_id = base_warehouse.id 
                    {$where} ORDER BY inventory.id DESC {$limit}";
            if ($is_download) return "SELECT {$fileds} FROM inventory LEFT JOIN base_warehouse ON inventory.warehouse_id = base_warehouse.id
                                      {$where} ORDER BY inventory.id DESC ";

            $list = ToolModel::queryData($sql);
            foreach ($list as &$value){
                if ($value['complete_time'] == "0000-00-00 00:00:00") $value['complete_time'] = "";
            }
            $data = array();
            $data['total'] = intval($countArr[0]['num']);
            $data['list'] = $list;

            return $data;
        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            throw new Exception($e->getMessage());
        }
    }


    /**
     * 导出数据
     * @param $param
     * @return array
     * @author duanwenjie
     */
    static public function export($param)
    {
        $sql = self::getList($param,true);
        $title = array(
            'inventory_sn' => '盘点单号',
            'warehouse_name' => '仓库',
            'type_name' => '盘点类型',
            'difference_quantity_all' => '盘点差异',
            'difference_money_all' => '差异金额',
            'operator' => '盘点人',
            'status_name' => '审核状态',
            'create_time' => '创建时间',
            'complete_time' => '完成时间'
        );

        $res = DownService::getData($sql,$title,'',$param);

        return $res;
    }


    /**
     * 获取盘点明细
     * @param $param
     * @return false|\PDOStatement|string|\think\Collection
     * @author duanwenjie
     */
    static public function getInventoryDetail($param)
    {
        $id = $param['id'];
        $res = self::getList($param);
        $res_detail = Db::name('inventory_detail')->field('*,inventory_quantity as original_inventory_quantity')->where('inventory_id','=',$id)->select();
        $data = array(
            "info" => $res['list'],
            "detail" => $res_detail
        );

        return $data;
    }


    /**
     * 审核通过/审核不通过 （通过的要生成盘盈盘亏数据记录，不通过的不需要）
     * @param $param
     * @return array
     * @author duanwenjie
     */
    static public function check($param)
    {
        /*status: 30 审核通过 40：审核不通过*/
        $data = array(
            "id" => $param['id'],
            "status" => $param['status'],
            "complete_time" => TIME
        );
        // 审核通过的要将差异数量在对应的仓库生成盘盈盘亏出入库数据
        if ($param['status'] == 30){
            /*仓库类型 1：店仓 ==> 店仓盘盈，盘亏出入库 2：国家仓 ==> 国家仓盘盈、盘亏出入库*/
            if ($param['ware_type'] == 1){
                $res1 = AllocateorderService::panYingPanKui(array("id" => $param['id']));
                if (!empty($res1) && $res1['code'] != 1) return returnArr('500', "店仓盘盈，盘亏出入库失败：原因 ".$res1['msg']);
            }else{
                $res2 = WarehousecountryService::inOutStockByInventory(array("id" => $param['id']));
                if (!empty($res2) && $res2['code'] != 1) return returnArr('500', "国家仓盘盈，盘亏出入库失败：原因 ".$res2['msg']);
            }
            $res1 = ToolModel::dbInsertOrUpdate('inventory', [$data]);
            return returnArr(1, '操作成功');
        }else{
            $res2 = ToolModel::dbInsertOrUpdate('inventory', [$data]);
            return returnArr(1, '操作成功');
        }
    }

    /**
     * 实时保存盘点单明细数据
     * @param $param
     * @author duanwenjie
     */
    static public function immediatelySave($param)
    {
        $param['difference_quantity'] = $param['inventory_quantity'] - $param['stock_quantity']; // 更新明细盘点差异数量
        $param['difference_money'] = $param['difference_quantity'] * $param['unit_price']; // 更新盘点差异金额
        unset($param['original_inventory_quantity'], $param['type'], $param['difference_quantity_all'], $param['difference_money_all']);
        $sql = "UPDATE inventory SET difference_quantity_all = (SELECT SUM(inventory_detail.difference_quantity) as 
                A FROM inventory_detail WHERE inventory_id = {$param['inventory_id']}),                    
                difference_money_all = (SELECT SUM(inventory_detail.difference_quantity*inventory_detail.unit_price) 
                as B FROM inventory_detail WHERE inventory_detail.inventory_id = {$param['inventory_id']})
                WHERE inventory.id = {$param['inventory_id']}";
        $res1 = ToolModel::execute($sql);
        $res2 = ToolModel::dbInsertOrUpdate('inventory_detail', [$param]);

        returnJson('1', '操作成功');
    }

    /**
     * 上传数据处理
     * @param $data
     * @param $param
     * @return array
     * @throws Exception
     * @author duanwenjie
     */
    static public function uploadHandle($data, $param)
    {
        try{
            $line = 1;
            $error = array();
            $skus = array();
            $data_ = array();
            foreach ($data as $itm){
                $line ++;
                if (empty(trim($itm[0]))) {
                    $error[] = '第' . $line . '行SKU为空';
                    continue;
                }
                if (empty(trim($itm[1]))) {
                    $error[] = '第' . $line . '行盘点数量为空';
                    continue;
                }

                $data_[trim($itm[0])] = trim($itm[1]);
                $skus[] = trim($itm[0]);
            }

            if (!empty($data_)) {
                $error1 = self::checkSkus($skus,$param['id']);
                if (!empty($error1)) $error = array_merge($error,$error1);
            }else{
                $error[] = "无有效数据";
            }
            if(!empty($error)){
                return ["code" => "500", "msg" => join('<br/>',$error)];
            }else{
                $res = array();
                $difference_quantity_all = 0;
                $difference_money_all = 0;
                if (!empty($data_)){
                    $res = self::checkSkus($skus,$param['id'],true);
                    foreach ($res as &$value){
                        $value['original_inventory_quantity'] = 0;
                        if (in_array($value['sku'],array_keys($data_))){
                            $value['inventory_quantity'] = $data_[$value['sku']]; // 盘点数量
                            $value['difference_quantity'] = $value['inventory_quantity'] - $value['stock_quantity']; // 盘点差异数量
                            $value['difference_money'] =  $value['difference_quantity'] * $value['unit_price']; // 差异金额
                            $difference_quantity_all += $value['difference_quantity'];
                            $difference_money_all += $value['difference_money'];
                        }else{
                            $value['inventory_quantity'] = ""; // 盘点数量
                            $value['difference_quantity'] = ""; // 盘点差异数量
                            $value['difference_money'] = ""; // 差异金额
                        }
                    }
                }
                return ["code" => "1", "msg" => "操作成功", "data" => $res, "info" => array("difference_quantity_all" => $difference_quantity_all, "difference_money_all" => $difference_money_all)];
            }
        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            throw new Exception($e->getMessage());
        }
    }


    /**
     * 校验上传的SKU是否在盘点明细中
     * @param $skus
     * @param $inventory_id
     * @param bool $is_needskuInfo
     * @return array|false|\PDOStatement|string|\think\Collection
     * @author duanwenjie
     */
    static public function checkSkus($skus, $inventory_id, $is_needskuInfo = false)
    {
        $error_msg = array();
        $res = Db::name('inventory_detail')->field('*')->where('inventory_id','=',$inventory_id)
            ->select();
        if ($is_needskuInfo) return $res;
        $diff_sku = array();
        if (!empty($res)){
            $exist_skus = array_column(array_values($res),'sku');
            $diff_sku = array_diff($skus,$exist_skus);
        }
        if (!empty($diff_sku)){
            $error_msg[] = "SKU：(".join(',',$diff_sku).")不在盘点单明细中";
        }

        return $error_msg;
    }

    /**
     * 保存/提审
     * @param $param
     * @author duanwenjie
     */
    static public function saveData($param)
    {
        $is_tishen = isset($param['is_tishen']) ? true : false;
        $inventory_data = array();
        $inventory_detail_data = array();
        if (!empty($param)){
            $inventory_data = array(
                "id" => $param['id'],
                "difference_quantity_all" => $param['difference_quantity_all'],
                "difference_money_all" => $param['difference_money_all']
            );
            $inventory_detail_data = json_decode($param['list'],true);
            foreach ($inventory_detail_data as &$val){
                unset($val['original_inventory_quantity'],$val['LAY_TABLE_INDEX']);
            }
        }
        /*是提审，状态变更为20：待审核*/
        if ($is_tishen){
            $inventory_data['status'] = 20;
        }
        if (!empty($inventory_data)) ToolModel::dbInsertOrUpdate('inventory',[$inventory_data]);
        if (!empty($inventory_detail_data)) ToolModel::dbInsertOrUpdate('inventory_detail',$inventory_detail_data);
        returnJson('1','操作成功');
    }

    /**
     * 获取盘点单明细
     * @param $param
     * @return mixed
     * @author duanwenjie
     */
    static public function getInventoryList($param)
    {
        $type = $param['type'];
        $base_warehouse_id = $param['base_warehouse_id'];
        $base_warehouse_id_sku = $param['base_warehouse_id_sku'];
        $sku = $param['sku'];
        $where = " where 1 = 1 ";
        if ($type == 1){
            $baseWarehouse = ToolModel::findByAttributes('base_warehouse', ['id' => $base_warehouse_id_sku]);
            if (!empty($base_warehouse_id_sku)){
                $base_warehouse_id_sku = trim($base_warehouse_id_sku);
                if ($baseWarehouse['type'] == 2){
                    //国家仓
                    $where .= " AND receipt_warehouse_id = $base_warehouse_id_sku";
                }else{
                    //店仓
                    $where .= " AND base_warehouse_id = $base_warehouse_id_sku";
                }
            }
            if (!empty($sku)){
                $sku = trim($sku);
                $where .= " AND sku = '$sku' ";
            }
        }else{
            $baseWarehouse = ToolModel::findByAttributes('base_warehouse', ['id' => $base_warehouse_id]);
            if (!empty($base_warehouse_id)){
                $base_warehouse_id = trim($base_warehouse_id);
                if ($baseWarehouse['type'] == 2){
                    //国家仓
                    $where .= " AND receipt_warehouse_id = $base_warehouse_id";
                }else{
                    //店仓
                    $where .= " AND base_warehouse_id = $base_warehouse_id";
                }
            }
        }
        if ($baseWarehouse['type'] == 2){
            //国家仓
            $sql = "SELECT *,receipt_warehouse_id as base_warehouse_id,'{$baseWarehouse['warehouse_name']}' as base_warehouse_name  
                    FROM warehouse_country_stock {$where}";
        }else{
            //店仓
            $sql = "SELECT * FROM shop_warehouse_stock {$where}";
        }
        $res = Db::query($sql);

        return $res;
    }

    /**
     * 通过国家获取仓库
     * @param $param
     * @return array
     * @author duanwenjie
     */
    static public function getWarehouseByCountry($param)
    {
        $base_country_id = $param['base_country_id'];
        $warehouse = Db::name('base_warehouse')
            ->field('*')
            ->where('base_country_id','=',$base_country_id)
            ->where('status','=',1)
            ->column('warehouse_name','id');

        return $warehouse;
    }

    /**
     * 生成盘点单
     * @param $param
     * @author duanwenjie
     */
    static public function saveInventory($param)
    {
        try{
            $type = isset($param['type']) ? $param['type'] : "";
            $base_warehouse_id = isset($param['base_warehouse_id']) ? $param['base_warehouse_id'] : "";
            $base_warehouse_id_sku = isset($param['base_warehouse_id_sku']) ? $param['base_warehouse_id_sku'] : "";
            $list = isset($param['list']) ? $param['list'] : array();
            $insert_inventory_data = array();
            $insert_inventory_detail_data = array();
            $insert_inventory_detail_data_ = array();
            /*仓库+SKU盘点*/
            if ($type == 1){
                if (empty($list)) returnJson('500', '盘点单明细不能为空');
                $baseWarehouse = ToolModel::findByAttributes('base_warehouse', ['id' => $base_warehouse_id_sku]);
                if ($baseWarehouse['type'] == 2){
                    //国家仓
                    $res = Db::name("warehouse_country_stock")
                        ->field('*,receipt_warehouse_id as base_warehouse_id,physical_stock,wam')
                        ->where('id', 'in', $list)
                        ->select();
                }else{
                    // 店仓
                    $res = Db::name("shop_warehouse_stock")->where('id', 'in', $list)->select();
                }
                if (!empty($res)){
                    $skus = array_unique(array_column(array_values($res),'sku'));
                    $skuInfo = SkuService::getBatchInfoBySku($skus,['ean']);
                    $inventory_sn = ToolModel::createSerialNumber('PD1'); // 盘点单号
                    $insert_inventory_data = array(
                        "inventory_sn" => $inventory_sn,
                        "warehouse_id" => $res[0]['base_warehouse_id'],
                        "difference_quantity_all" => 0,
                        "difference_money_all" => 0,
                        "type" => 20, // SKU盘点
                        "operator" => CN_NAME ? CN_NAME : 'admin'
                    );
                    $inventory_id = ToolModel::insertData($insert_inventory_data, 'inventory');
                    if ($inventory_id){
                        foreach ($res as $value){
                            $insert_inventory_detail_data[] = array(
                                "inventory_id" => $inventory_id,
                                "sku" => $value['sku'],
                                "sku_name" => $value['sku_name'] ?? '',
                                "ean" => $skuInfo[$value['sku']]['ean'] ?? '',
                                "stock_quantity" => $value['physical_stock'], // 库存数量 =》 实物库存
                                "unit_price" => $value['wam'],
                                "inventory_quantity" => $value['physical_stock'], // 盘点数量 =》 实物库存
                                "difference_quantity" => 0,
                                "difference_money" => 0
                            );
                        }
                        ToolModel::dbInsertOrUpdate('inventory_detail', $insert_inventory_detail_data);
                    }
                }else{
                    returnJson('500', '店仓/国家仓库存列表中没有合适的数据进行盘点');
                }
            }else{
                if (empty($base_warehouse_id)) returnJson('500', '盘点仓库不能为空');
                $baseWarehouse_ = ToolModel::findByAttributes('base_warehouse', ['id' => $base_warehouse_id]);
                if ($baseWarehouse_['type'] == 2){
                    //国家仓
                    $res_= Db::name("warehouse_country_stock")
                        ->field('*,receipt_warehouse_id as base_warehouse_id,physical_stock,wam')
                        ->where('receipt_warehouse_id', '=', $base_warehouse_id)
                        ->select();
                }else{
                    // 店仓
                    $res_ = Db::name("shop_warehouse_stock")->where('base_warehouse_id', '=', $base_warehouse_id)->select();
                }
                if (!empty($res_)){
                    $skus_ = array_unique(array_column(array_values($res_),'sku'));
                    $skuInfo_ = SkuService::getBatchInfoBySku($skus_,['ean']);
                    $inventory_sn_ = ToolModel::createSerialNumber('PD1'); // 盘点单号
                    $insert_inventory_data_ = array(
                        "inventory_sn" => $inventory_sn_,
                        "warehouse_id" => $res_[0]['base_warehouse_id'],
                        "difference_quantity_all" => 0,
                        "difference_money_all" => 0,
                        "type" => 10, // 全库盘点
                        "operator" => CN_NAME ? CN_NAME : 'admin'
                    );
                    $inventory_id_ = ToolModel::insertData($insert_inventory_data_, 'inventory');
                    if ($inventory_id_){
                        foreach ($res_ as $value_){
                            $insert_inventory_detail_data_[] = array(
                                "inventory_id" => $inventory_id_,
                                "sku" => $value_['sku'],
                                "sku_name" => $value_['sku_name'] ?? '',
                                "ean" => $skuInfo_[$value_['sku']]['ean'] ?? '',
                                "stock_quantity" => $value_['physical_stock'], // 库存数量 =》 实物库存
                                "unit_price" => $value_['wam'],
                                "inventory_quantity" => $value_['physical_stock'], // 盘点数量 =》 实物库存
                                "difference_quantity" => 0,
                                "difference_money" => 0
                            );
                        }
                        ToolModel::dbInsertOrUpdate('inventory_detail', $insert_inventory_detail_data_);
                    }
                }else{
                    returnJson('500', '店仓/国家仓库存列表中没有合适的数据进行盘点');
                }
            }
            returnJson('1', '操作成功');
        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            returnJson('600', $e->getMessage());
        }
    }
}